/*
 * Copyright (C) 2009 Dimitrios C. Michalakos
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package com.restsql.db.sql;


import com.restsql.db.ColumnInfo;
import com.restsql.db.Database;
import com.restsql.util.collection.MultiHashValuedMap;
import java.sql.SQLException;
import java.util.Set;

/**
 * MySQLDesigner can be used for designing SQL commands for MySQL databases.
 * @author dimitris@jmike.gr
 */
public class MySQLDesigner extends GenericSQLDesigner implements SQLDesigner {

    public MySQLDesigner(Database db) {
        super(db);
    }

    /**
     * Constructs a Select SQL command.
     * @return
     * @throws java.sql.SQLException
     * @throws InvalidInputDataException
     */
    @Override
    public String getSelectSQL() throws SQLException, InvalidInputDataException {
        final boolean qualified = tableCollection.size() > 1;//indicates whether column names should be qualified (i.e. table.column) or not (i.e. column)
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");
        /* append columns */
        for (int i = 0; i < columnCollection.size(); i++) {//iterate columns
            if (i > 0) {//a column already exists
                sql.append(", ");//append comma
            }
            if (qualified) {//column names must be qualified
                sql.append("`" + columnCollection.get(i).getTable() + "`." + columnCollection.get(i).getName());//append column
            } else {//column names can be unqualified
                sql.append("`" + columnCollection.get(i).getName() + "`");//append column
            }
        }
        /* append FROM clause */
        sql.append(" FROM `" + tableCollection.get(0) + "`");//append the first table in source
        for (int i = 1; i < tableCollection.size(); i++) {//iterate tables starting from the second table in source
            final String table1 = tableCollection.get(i - 1);
            final String table2 = tableCollection.get(i);
            switch (joinCollection.get(i - 1)) {
                case INNER: {
                    sql.append(" INNER JOIN");
                    break;
                }
                case OUTER: {
                    sql.append(" OUTER JOIN");
                    break;
                }
                case LEFT: {
                    sql.append(" LEFT OUTER JOIN");
                    break;
                }
                case RIGHT: {
                    sql.append(" RIGHT OUTER JOIN");
                    break;
                }
            }
            sql.append(" `" + table2 + "`");
            sql.append(" ON");
            String[][] onColumns = this.db.getForeignKeyInfo().getColumns(table1, table2);
            for (int j = 0; j < onColumns.length; j++) {
                sql.append(" `" + table1 + "`." + onColumns[j][0] + " = `" + table2 + "`." + onColumns[j][1]);
                if (j < (onColumns.length - 1)) {
                    sql.append(" AND");
                }
            }
        }
        /* append WHERE clause */
        if (filters.size() != 0 || filterBoundaries.size() != 0) {//query contains filter or filterBoundaries
            sql.append(" WHERE ");
            if (filters.size() != 0 && filterBoundaries.size() > 1) {//query contains filter and more that one filterBoundaries
                sql.append("(");
            }
            for (int i = 0; i < filterBoundaries.size(); i++) {//iterate filter boundaries
                Filter f = filterBoundaries.get(i);
                if (qualified) {//column names must be qualified
                    sql.append("`" + f.getTable() + "`." + f.getColumn());
                } else {//column names can be unqualified
                    sql.append("`" + f.getColumn() + "`");
                }
                sql.append(" = " + f.getValue());
                if (i < (filterBoundaries.size() - 1)) {//more columns exist
                    sql.append(" AND ");//append AND to sql
                }
            }
            if (filters.size() != 0 && filterBoundaries.size() > 1) {//query contains filter and more that one filterBoundaries
                sql.append(")");
            }
            if (filters.size() != 0 && filterBoundaries.size() != 0) {//query contains filter and filterBoundaries
                sql.append(" AND ");//append AND to sql
            }
            if (filters.size() > 1 && filterBoundaries.size() != 0) {//query contains filterBoundaries and more than one filters
                sql.append("(");
            }
            for (int i = 0; i < filters.size(); i++) {//iterate filter
                Filter f = filters.get(i);
                if (qualified) {//column names must be qualified
                    sql.append("`" + f.getTable() + "`." + f.getColumn());
                } else {//column names can be unqualified
                    sql.append("`" + f.getColumn() + "`");
                }
                sql.append(" = " + f.getValue());
                if (i < (filters.size() - 1)) {//more columns exist
                    sql.append(" AND ");//append comma to sql
                }
            }
            if (filters.size() > 1 && filterBoundaries.size() != 0) {//query contains filterBoundaries and more than one filters
                sql.append(")");
            }
        }
        /* append ORDER BY clause */
        if (orderCollection.size() != 0) {//query contains order statements
            sql.append(" ORDER BY ");
            for (int i = 0; i < orderCollection.size(); i++) {//iterate columns
                if (qualified) {//column names must be qualified
                    sql.append("`" + orderCollection.get(i).getTable() + "`." + orderCollection.get(i).getColumn());//use table.column format to distinguish the columns
                } else {//column names can be unqualified
                    sql.append("`" + orderCollection.get(i).getColumn() + "`");
                }
                if (orderCollection.get(i).isAsc()) {
                    sql.append(" ASC");
                } else {
                    sql.append(" DESC");
                }
                if (i < (orderCollection.size() - 1)) {//more columns exist
                    sql.append(", ");//append comma to the sql
                }
            }
        }
        /* append page limit */
        sql.append(" LIMIT " + String.valueOf(pageIndex - 1) + "," + String.valueOf(pageSize) + ";");
        /* return sql command as a String */
        return sql.toString();
    }

    /**
     * Constructs a Insert SQL command.
     * @return
     * @throws java.sql.SQLException
     * @throws InvalidInputDataException
     */
    @Override
    public String getInsertSQL() throws SQLException, InvalidInputDataException {
        MultiHashValuedMap boundaries = getFilterBoundariesMap();
        StringBuilder sql = new StringBuilder();
        for (int i = 0; i < tableCollection.size(); i++) {//iterate the tables of the query
            final String table = tableCollection.get(i);//get current table name
            StringBuilder cs = new StringBuilder();//String buffer for columns part
            StringBuilder vs = new StringBuilder();//String buffer for values part
            for (int j = 0; j < columnCollection.size(); j++) {//iterate columns
                Column column = columnCollection.get(j);//get current column
                if (column.getTable().equals(table)) {//current column belongs to current table
                    ColumnInfo columnInfo = db.getColumnInfo(table);
                    if (!columnInfo.isAutoIncremented(column.getName())) {//this columns is not auto incremented
                        final String key = column.getTable() + "." + column.getName();
                        final String value;
                        switch (boundaries.valueSize(key)) {
                            case 0: {//this column is not bounded
                                value = values.get(key).get(0);
                                break;
                            }
                            case 1: {//this column is bounded - can only contain a specific value
                                value = boundaries.getSingle(key);
                                break;
                            }
                            default: {//this column is bounded - can contain one of a values set
                                Set<String> set = boundaries.get(key);
                                if (set.contains(values.get(key).get(0))) {
                                    value = values.get(key).get(0);
                                } else {
                                    value = set.iterator().next();
                                }
                                break;
                            }
                        }
                        if (cs.length() > 0) {
                            cs.append(", ");//append comma
                            vs.append(", ");//append comma
                        }
                        cs.append("`" + column.getName() + "`");//append column
                        vs.append(MySQLInputData.parse(db, column.getTable(), column.getName(), value));//append value
                    }
                }
            }
            sql.append("INSERT INTO `" + table + "` (");
            sql.append(cs);
            sql.append(") VALUES (");
            sql.append(vs);
            sql.append(");");
        }
        return sql.toString();
    }

}
